﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace program1
{
    public class SchoolRepository
    {
        private string connectionString;

        public SchoolRepository(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public void AddSchool(string name, string location)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("INSERT INTO Schools (Name, Location) VALUES (@Name, @Location)", connection))
                {
                    command.Parameters.AddWithValue("@Name", name);
                    command.Parameters.AddWithValue("@Location", location);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void DeleteSchool(int schoolID)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("DELETE FROM Schools WHERE SchoolID = @SchoolID", connection))
                {
                    command.Parameters.AddWithValue("@SchoolID", schoolID);
                    command.ExecuteNonQuery();
                }
            }
        }

        public void UpdateSchool(int schoolID, string newName, string newLocation)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("UPDATE Schools SET Name = @NewName, Location = @NewLocation WHERE SchoolID = @SchoolID", connection))
                {
                    command.Parameters.AddWithValue("@NewName", newName);
                    command.Parameters.AddWithValue("@NewLocation", newLocation);
                    command.Parameters.AddWithValue("@SchoolID", schoolID);
                    command.ExecuteNonQuery();
                }
            }
        }

        public List<School> GetAllSchools()
        {
            List<School> schools = new List<School>();

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Schools", connection))
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        School school = new School
                        {
                            SchoolID = reader.GetInt32(reader.GetOrdinal("SchoolID")),
                            Name = reader.GetString(reader.GetOrdinal("Name")),
                            Location = reader.GetString(reader.GetOrdinal("Location"))
                        };
                        schools.Add(school);
                    }
                }
            }

            return schools;
        }


        public School GetSchoolById(int schoolId)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Schools WHERE SchoolID = @SchoolID", connection))
                {
                    command.Parameters.AddWithValue("@SchoolID", schoolId);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            School school = new School
                            {
                                SchoolID = reader.GetInt32(reader.GetOrdinal("SchoolID")),
                                Name = reader.GetString(reader.GetOrdinal("Name")),
                                Location = reader.GetString(reader.GetOrdinal("Location"))
                            };

                            return school;
                        }
                    }
                }
            }

            return null;
        }



    }
}
